package ua.kiev.tosik.price.database.dao;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.text.StrBuilder;
import org.apache.commons.lang.text.StrTokenizer;
import org.apache.commons.lang.time.DurationFormatUtils;
import org.apache.log4j.Logger;
import ua.kiev.tosik.price.database.RowSort;
import ua.kiev.tosik.price.database.connectors.ConnectorFactory;
import ua.kiev.tosik.price.database.connectors.IConnector;
import ua.kiev.tosik.price.models.Clients;
import ua.kiev.tosik.price.models.Prices;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class PricesDaoDerby implements IPricesDao {

	private Logger logger = Logger.getLogger("ua.kiev.tosik.price.database.dao");
	private Connection conn;
	private final String tableName, colNames;
	private String sqlSelect, countSelect;

	private HashMap<Integer,String> sortColumnMap;
	private HashMap<String,String> sortColumnType;
	private RowSort rowSort;

	private int limiter = 300;
	private int offset = 0;
	private int recordCount = 0;
	// private PreparedStatement selectPrepared;
	private PreparedStatement insertPrepared;

	private PreparedStatement findByAndPs;
	private String findByAndSql = "";

	public PricesDaoDerby() {
		logger.debug("Initialize PricesDao for Derby");
		tableName = "PRICES";
		colNames = "ID, CLIENTS_ID, PRICES_FILES_ID, ARTICLE_INTERNAL, ARTICLE, NAME, PRICE, PRICES_DATE, CREATE_DATE, EXISTENCE";
		sqlSelect = "SELECT A.ID, A.CLIENTS_ID, A.PRICES_FILES_ID, A.ARTICLE_INTERNAL, A.ARTICLE, A.NAME, A.PRICE, A.PRICES_DATE, A.CREATE_DATE, B.NAME AS CLIENTS_NAME, A.EXISTENCE FROM " + tableName + " AS A INNER JOIN CLIENTS AS B ON (A.CLIENTS_ID = B.ID)";
		countSelect = "SELECT COUNT(A.ID) AS COUNT FROM " + tableName + " AS A INNER JOIN CLIENTS AS B ON (A.CLIENTS_ID = B.ID)";

		sortColumnMap = new HashMap<Integer,String>();
		sortColumnMap.put(1, "UPPER(B.NAME)");
		sortColumnMap.put(2, "UPPER(A.ARTICLE_INTERNAL)");
		sortColumnMap.put(3, "UPPER(A.ARTICLE)");
		sortColumnMap.put(4, "UPPER(A.NAME)");
		sortColumnMap.put(5, "A.PRICE");
		sortColumnMap.put(6, "A.PRICES_DATE");
		sortColumnMap.put(7, "UPPER(A.EXISTENCE)");

		sortColumnType = new HashMap<String, String>();
		sortColumnType.put("ASCENDING", "ASC");
		sortColumnType.put("DESCENDING", "DESC");

		rowSort = new RowSort();
		rowSort.setColumn(4);
		// rowSort.setType("ASCENDING"); // By default


		try {
			IConnector connector = ConnectorFactory.getInstance("Derby");
			conn = connector.getConn();
			// selectPrepared = conn.prepareStatement(sqlSelect + " ORDER BY A.PRICES_DATE DESC OFFSET ? ROWS FETCH FIRST ? ROWS ONLY");
		} catch (Exception e) {
			logger.fatal(e.getMessage(), e);
		}
	}

	@Override
	public Connection getConn() {
		return conn;
	}

	@Override
	public int countAll() {
		int count = 0;
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT COUNT(ID) AS TOTAL FROM PRICES");
			if ( rs.next() ) {
				count = rs.getInt("total");
			}
		} catch (Exception e) {
			logger.debug(e);
		}
		return count;
	}

	@Override
	public List<Prices> findByAny(Prices prices) {
		long startTime = System.currentTimeMillis();
		List<Prices> pricesList = new ArrayList<Prices>();
		if ( prices == null ) { return pricesList; }
		try {
			List<String> sqlWhere = new ArrayList<String>();
			String WHERE = "";
			if ( prices.getName() != null && !prices.getName().trim().equals("") ) {
				prices.setName( StringUtils.replaceChars(prices.getName(), "*", "%") );
				StrBuilder sb = new StrBuilder(prices.getName());
				StrTokenizer st = sb.asTokenizer();
				String[] names = st.getTokenArray();
				StrBuilder sqlForName = new StrBuilder();
				for ( String s: names ) {
					sqlForName.appendSeparator(" AND ");
					sqlForName.append("UPPER(A.NAME) LIKE UPPER('%" + StringEscapeUtils.escapeSql(s) + "%')");
				}
				if ( sqlForName.length() > 0 ) {
					sqlWhere.add("(" + sqlForName.toString() + ")");
				}
			}
			if ( prices.getArticle() != null && !prices.getArticle().equals("") ) {
				prices.setArticle(StringUtils.replaceChars(prices.getArticle(), "*", "%"));
				sqlWhere.add("UPPER(A.ARTICLE) LIKE UPPER('" + StringEscapeUtils.escapeSql(prices.getArticle().trim()) + "%')");
			}
			if ( prices.getArticleInternal() != null && !prices.getArticleInternal().equals("") ) {
				prices.setArticle(StringUtils.replaceChars(prices.getArticleInternal(), "*", "%"));
				sqlWhere.add("UPPER(A.ARTICLE_INTERNAL) LIKE UPPER('" + StringEscapeUtils.escapeSql(prices.getArticleInternal().trim()) + "%')");
			}

			if ( sqlWhere.size() > 0 ) {
				WHERE = " WHERE " + StringUtils.join(sqlWhere, " OR ");
			}

			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(countSelect + WHERE);
			if ( rs.next() ) {
				setRecordCount(rs.getInt("count"));
			}
			rs.close();
			st.close();
			// logger.debug("SQL: " + sqlSelect + "\n" + WHERE + " ORDER BY " + sortColumnMap.get(rowSort.getColumn()) + " " + sortColumnType.get(rowSort.getType()));

			String SQL = sqlSelect + WHERE + " ORDER BY " + sortColumnMap.get(rowSort.getColumn()) + " " + sortColumnType.get(rowSort.getType()) + " OFFSET ? ROWS FETCH FIRST ? ROWS ONLY";
			if ( findByAndPs == null || !findByAndSql.equals(SQL) ) {
				if ( findByAndPs != null ) {
					findByAndPs.close();
					logger.debug("Recreate Prepared Statement");
				}
				findByAndPs = conn.prepareStatement(SQL);
				findByAndSql = SQL;
			}
			findByAndPs.setInt(1, getOffset());
			findByAndPs.setInt(2, getLimiter());
			rs = findByAndPs.executeQuery();
			while ( rs.next() ) {
				pricesList.add(fillRecord(rs));
			}
			rs.close();
			// findByAndPs.close();
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			e.printStackTrace();
		}
		logger.debug("Search time: " + DurationFormatUtils.formatDuration((System.currentTimeMillis()-startTime), "s.S") + " sec");
		return pricesList;
	}

	@Override
	public List<Prices> findAll() {
		// if ( selectPrepared == null ) { return null; }
		List<Prices> pricesList = new ArrayList<Prices>();
//		int counter = 0;
//		setRecordCount(counter);
//		try {
//			selectPrepared.clearParameters();
//			selectPrepared.setInt(1, getOffset());
//			selectPrepared.setInt(2, getLimiter());
//			ResultSet rs = selectPrepared.executeQuery();
//			while ( rs.next() ) {
//				pricesList.add(fillRecord(rs));
//				counter++;
//			}
//			setRecordCount(counter);
//			rs.close();
//		} catch (Exception e) {
//			logger.error(e.getMessage(), e);
//		}
		return pricesList;
	}

	private Prices fillRecord(ResultSet rs) throws SQLException {
		Prices prices = new Prices();
		prices.setId(rs.getInt("id"));
		prices.setClientsId(rs.getInt("clients_id"));
		prices.setPricesFilesId(rs.getInt("prices_files_id"));
		prices.setArticleInternal(rs.getString("article_internal"));
		prices.setArticle(rs.getString("article"));
		prices.setName(rs.getString("name"));
		prices.setPrice(rs.getDouble("price"));
		prices.setPricePercent(rs.getDouble("price"));
		prices.setPricesDate(rs.getDate("prices_date"));
		prices.setCreateDate(rs.getDate("create_date"));
		prices.setClientsName(rs.getString("clients_name"));
		prices.setExistence(rs.getString("existence"));
		return prices;
	}

	@Override
	public Prices findById(int id) {
		Prices prices = null;
		try {
			PreparedStatement pst = conn.prepareStatement("SELECT " + colNames + " FROM " + tableName + " AS A WHERE ID = ?");
			pst.setInt(1, id);
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				prices = new Prices();
				prices.setId(rs.getInt("id"));
				prices.setClientsId(rs.getInt("clients_id"));
				prices.setPricesFilesId(rs.getInt("prices_files_id"));
				prices.setArticleInternal(rs.getString("article_internal"));
				prices.setArticle(rs.getString("article"));
				prices.setName(rs.getString("name"));
				prices.setPrice(rs.getDouble("price"));
				prices.setPricePercent(rs.getDouble("price"));
				prices.setPricesDate(rs.getDate("prices_date"));
				prices.setCreateDate(rs.getTimestamp("create_date"));
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			logger.error(e);
		}
		return prices;
	}

	@Override
	public List<Prices> findByDate(Date priceDate) {
		return null;
	}

	@Override
	public List<Prices> findByClientsId(Clients client) {
		return null;
	}

	@Override
	public List<Prices> findByNameAndArticle(String searchString) {
		return null;
	}

	@Override
	public void saveBatch(String fileName) throws Exception {
		conn.setAutoCommit(false);
		PreparedStatement ps = conn.prepareStatement("CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, ?, ?, null, ?, null, null, null,0)");
		ps.setString(1, "PRICES");
		ps.setString(2, "CLIENTS_ID, PRICES_FILES_ID, ARTICLE_INTERNAL, ARTICLE, NAME, PRICE, PRICES_DATE, EXISTENCE");
		ps.setString(3, fileName);
		ps.executeUpdate();
		ps.close();
		conn.setAutoCommit(true);
	}

	/**
	 *
	 * @param prices Prices object to insert
	 * @return Prices - created record
	 * @throws Exception if something wrong
	 */
	@Override
	public Prices save(Prices prices) throws Exception {
		if ( prices == null ) {
			throw new Exception("Can't save null record");
		}
		if ( insertPrepared == null ) {
			try {
				insertPrepared = conn.prepareStatement("INSERT INTO " + tableName + " (CLIENTS_ID, PRICES_FILES_ID, ARTICLE_INTERNAL, ARTICLE, NAME, PRICE, PRICES_DATE, EXISTENCE) VALUES(?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
			} catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new Exception(e);
			}
		}
		try {
			insertPrepared.clearParameters();
			prepareDataForCreation(insertPrepared, prices);
			insertPrepared.executeUpdate();
			ResultSet rs = insertPrepared.getGeneratedKeys();
			/*
			if ( rs.next() ) {
				int newId = rs.getInt(1);
				prices = findById(newId);
			}
			*/
			rs.close();
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new Exception(e);
		}
		return prices;
	}

	@Override
	public void deleteByPricesFilesId(int pricesFilesId) throws Exception {
		conn.setAutoCommit(false);
		try {
			PreparedStatement pst = conn.prepareStatement("DELETE FROM " + tableName + " WHERE PRICES_FILES_ID = ?");
			pst.setInt(1, pricesFilesId);
			pst.executeUpdate();
			pst.close();
			pst = conn.prepareStatement("UPDATE PRICES_FILES SET PARSED = ? WHERE ID = ?");
			pst.setInt(1, 0);
			pst.setInt(2, pricesFilesId);
			pst.executeUpdate();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
	}

	public static java.sql.Date getSqlDate(Date date) {
		return new java.sql.Date(date.getTime());
	}

	@Override
	public int getOffset() {
		return offset;
	}
	@Override
	public void setOffset(int offset) {
		if ( offset < 0 ) { offset = 0; }
		this.offset = offset;
	}
	@Override
	public int getLimiter() {
		return limiter;
	}
	@Override
	public void setLimiter(int limiter) {
		this.limiter = limiter;
	}

	@Override
	public void setRowSorter(RowSort rowSort) {
		if ( rowSort.getColumn() > 0 ) {
			this.rowSort = rowSort;
		}
	}

	@Override
	public int getRecordCount() {
		return recordCount;
	}

	private void setRecordCount(int recordCount) {
		this.recordCount = recordCount;
	}

	private void prepareDataForCreation(PreparedStatement ps, Prices prices) throws Exception {
		// int clientsId = prices.getClientsId();
		// int pricesFilesId = prices.getPricesFilesId();
		String articleInternal = StringUtils.defaultString(prices.getArticleInternal()).trim();
		String article = StringUtils.defaultString(prices.getArticle()).trim();
		String name = StringUtils.defaultString(prices.getName()).trim();
		String existence = StringUtils.defaultString(prices.getExistence()).trim();
		// double price = prices.getPrice();
		// Date priceDate = prices.getPricesDate();

		ps.setInt(1, prices.getClientsId());
		ps.setInt(2, prices.getPricesFilesId());
		if ( articleInternal.equals("") ) { ps.setNull(3, Types.VARCHAR); } else { ps.setString(3, articleInternal); }
		if ( article.equals("") ) { ps.setNull(4, Types.VARCHAR); } else { ps.setString(4, article); }
		if ( name.equals("") ) { ps.setNull(5, Types.VARCHAR); } else { ps.setString(5, name); }
		ps.setDouble(6, prices.getPrice());
		ps.setDate(7, getSqlDate(prices.getPricesDate()));
		if ( existence.equals("") ) { ps.setNull(8, Types.VARCHAR); } else { ps.setString(8, existence); }
	}

}
